/*
Navicat MySQL Data Transfer

Source Server         : hemant
Source Server Version : 50511
Source Host           : localhost:3306
Source Database       : thesurya_portal

Target Server Type    : MYSQL
Target Server Version : 50511
File Encoding         : 65001

Date: 2011-07-27 19:44:29
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `action`
-- ----------------------------
DROP TABLE IF EXISTS `action`;
CREATE TABLE `action` (
`module_id`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`controller_id`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`action_id`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
PRIMARY KEY (`action_id`, `controller_id`, `module_id`),
FOREIGN KEY (`controller_id`, `module_id`) REFERENCES `controller` (`controller_id`, `module_id`) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Records of action
-- ----------------------------
BEGIN;
INSERT DELAYED INTO `action` (`module_id`, `controller_id`, `action_id`) VALUES ('default', 'aboutus', 'index'), ('agent', 'account', 'network'), ('admin', 'agent', 'cansponsor'), ('admin', 'agent', 'disburse'), ('admin', 'agent', 'dispatch'), ('admin', 'agent', 'index'), ('admin', 'agent', 'pendingdisburse'), ('admin', 'agent', 'register'), ('default', 'agent', 'checkuid'), ('default', 'agent', 'imod'), ('default', 'contactus', 'index'), ('employee', 'estate', 'index'), ('employee', 'estate', 'register'), ('admin', 'index', 'index'), ('agent', 'index', 'index'), ('default', 'index', 'index'), ('employee', 'index', 'index'), ('agent', 'network', 'index'), ('default', 'redirect', 'index'), ('default', 'search', 'index'), ('default', 'search', 'result'), ('default', 'sell', 'index'), ('default', 'sell', 'register');
COMMIT;

-- ----------------------------
-- Table structure for `auth_user`
-- ----------------------------
DROP TABLE IF EXISTS `auth_user`;
CREATE TABLE `auth_user` (
`user_id`  varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`sec_passwd`  char(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`user_salt`  char(6) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`user_type_id`  varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`valid_from`  timestamp NULL DEFAULT NULL ,
`valid_upto`  timestamp NULL DEFAULT NULL ,
`is_active`  tinyint(1) NULL DEFAULT 1 COMMENT '		' ,
`remarks`  varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`user_id`),
FOREIGN KEY (`user_type_id`) REFERENCES `user_type` (`user_type_id`) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Records of auth_user
-- ----------------------------
BEGIN;
INSERT DELAYED INTO `auth_user` (`user_id`, `sec_passwd`, `user_salt`, `user_type_id`, `valid_from`, `valid_upto`, `is_active`, `remarks`) VALUES ('administrator', 'admin', null, 'CPNY', null, null, null, null), ('AGNT-A-100', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-137', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-138', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-139', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-140', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-141', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-142', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-143', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-144', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-145', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-146', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-2', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-34', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-35', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-36', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-37', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-38', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-39', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-40', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-51', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-52', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-53', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-54', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-55', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-56', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-57', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-58', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-59', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-60', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-61', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-62', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-63', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-64', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-65', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-66', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-67', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-68', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-69', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-70', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-71', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-72', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-73', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-74', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-75', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-76', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-77', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-78', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-79', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-80', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-81', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-82', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-83', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-84', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-85', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-86', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-87', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-94', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-95', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-96', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-97', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-98', 'asdf', null, 'agnt', null, null, '1', null), ('AGNT-A-99', 'asdf', null, 'agnt', null, null, '1', null);
COMMIT;

-- ----------------------------
-- Table structure for `controller`
-- ----------------------------
DROP TABLE IF EXISTS `controller`;
CREATE TABLE `controller` (
`module_id`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`controller_id`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
PRIMARY KEY (`controller_id`, `module_id`),
FOREIGN KEY (`module_id`) REFERENCES `module` (`module_id`) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Records of controller
-- ----------------------------
BEGIN;
INSERT DELAYED INTO `controller` (`module_id`, `controller_id`) VALUES ('admin', 'agent'), ('admin', 'index'), ('agent', 'account'), ('agent', 'index'), ('agent', 'network'), ('default', 'aboutus'), ('default', 'agent'), ('default', 'contactus'), ('default', 'index'), ('default', 'joinus'), ('default', 'panel'), ('default', 'redirect'), ('default', 'search'), ('default', 'sell'), ('employee', 'estate'), ('employee', 'index');
COMMIT;

-- ----------------------------
-- Table structure for `role`
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
`role_id`  varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`role_name`  varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`role_id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Records of role
-- ----------------------------
BEGIN;
INSERT DELAYED INTO `role` (`role_id`, `role_name`) VALUES ('admin', 'Admin'), ('agent', 'Agent'), ('employee', 'Employee'), ('guest', 'guest');
COMMIT;

-- ----------------------------
-- Table structure for `role_resource`
-- ----------------------------
DROP TABLE IF EXISTS `role_resource`;
CREATE TABLE `role_resource` (
`role_id`  varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`module_id`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`controller_id`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`action_id`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
PRIMARY KEY (`role_id`, `action_id`, `controller_id`, `module_id`),
FOREIGN KEY (`action_id`, `controller_id`, `module_id`) REFERENCES `action` (`action_id`, `controller_id`, `module_id`) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (`role_id`) REFERENCES `role` (`role_id`) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Records of role_resource
-- ----------------------------
BEGIN;
INSERT DELAYED INTO `role_resource` (`role_id`, `module_id`, `controller_id`, `action_id`) VALUES ('admin', 'admin', 'agent', 'cansponsor'), ('admin', 'admin', 'agent', 'disburse'), ('admin', 'admin', 'agent', 'dispatch'), ('admin', 'admin', 'agent', 'index'), ('admin', 'employee', 'estate', 'index'), ('admin', 'admin', 'index', 'index'), ('admin', 'admin', 'agent', 'pendingdisburse'), ('admin', 'admin', 'agent', 'register'), ('admin', 'employee', 'estate', 'register'), ('agent', 'agent', 'index', 'index'), ('agent', 'agent', 'network', 'index'), ('agent', 'agent', 'account', 'network'), ('employee', 'employee', 'index', 'index'), ('guest', 'default', 'aboutus', 'index'), ('guest', 'default', 'contactus', 'index'), ('guest', 'default', 'index', 'index'), ('guest', 'default', 'redirect', 'index'), ('guest', 'default', 'search', 'index'), ('guest', 'default', 'sell', 'index'), ('guest', 'default', 'sell', 'register'), ('guest', 'default', 'search', 'result');
COMMIT;

-- ----------------------------
-- Table structure for `user_role`
-- ----------------------------
DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role` (
`user_id`  varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`role_id`  varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
PRIMARY KEY (`role_id`, `user_id`),
FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`user_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (`role_id`) REFERENCES `role` (`role_id`) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Records of user_role
-- ----------------------------
BEGIN;
INSERT DELAYED INTO `user_role` (`user_id`, `role_id`) VALUES ('administrator', 'admin'), ('AGNT-A-100', 'agent'), ('AGNT-A-137', 'agent'), ('AGNT-A-138', 'agent'), ('AGNT-A-139', 'agent'), ('AGNT-A-140', 'agent'), ('AGNT-A-141', 'agent'), ('AGNT-A-142', 'agent'), ('AGNT-A-143', 'agent'), ('AGNT-A-144', 'agent'), ('AGNT-A-145', 'agent'), ('AGNT-A-146', 'agent'), ('AGNT-A-2', 'agent'), ('AGNT-A-34', 'agent'), ('AGNT-A-35', 'agent'), ('AGNT-A-36', 'agent'), ('AGNT-A-37', 'agent'), ('AGNT-A-38', 'agent'), ('AGNT-A-39', 'agent'), ('AGNT-A-40', 'agent'), ('AGNT-A-51', 'agent'), ('AGNT-A-52', 'agent'), ('AGNT-A-53', 'agent'), ('AGNT-A-54', 'agent'), ('AGNT-A-55', 'agent'), ('AGNT-A-56', 'agent'), ('AGNT-A-57', 'agent'), ('AGNT-A-58', 'agent'), ('AGNT-A-59', 'agent'), ('AGNT-A-60', 'agent'), ('AGNT-A-61', 'agent'), ('AGNT-A-62', 'agent'), ('AGNT-A-63', 'agent'), ('AGNT-A-64', 'agent'), ('AGNT-A-65', 'agent'), ('AGNT-A-66', 'agent'), ('AGNT-A-67', 'agent'), ('AGNT-A-68', 'agent'), ('AGNT-A-69', 'agent'), ('AGNT-A-70', 'agent'), ('AGNT-A-71', 'agent'), ('AGNT-A-72', 'agent'), ('AGNT-A-73', 'agent'), ('AGNT-A-74', 'agent'), ('AGNT-A-75', 'agent'), ('AGNT-A-76', 'agent'), ('AGNT-A-77', 'agent'), ('AGNT-A-78', 'agent'), ('AGNT-A-79', 'agent'), ('AGNT-A-80', 'agent'), ('AGNT-A-81', 'agent'), ('AGNT-A-82', 'agent'), ('AGNT-A-83', 'agent'), ('AGNT-A-84', 'agent'), ('AGNT-A-85', 'agent'), ('AGNT-A-86', 'agent'), ('AGNT-A-87', 'agent'), ('AGNT-A-94', 'agent'), ('AGNT-A-95', 'agent'), ('AGNT-A-96', 'agent'), ('AGNT-A-97', 'agent'), ('AGNT-A-98', 'agent'), ('AGNT-A-99', 'agent');
COMMIT;

-- ----------------------------
-- Table structure for `user_type`
-- ----------------------------
DROP TABLE IF EXISTS `user_type`;
CREATE TABLE `user_type` (
`user_type_id`  varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`user_type_name`  varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`user_type_id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Records of user_type
-- ----------------------------
BEGIN;
INSERT DELAYED INTO `user_type` (`user_type_id`, `user_type_name`) VALUES ('AGNT', 'Agent'), ('CPNY', 'Company Employee');
COMMIT;

-- ----------------------------
-- Indexes structure for table `action`
-- ----------------------------
CREATE INDEX `fk_action_controller1` ON `action`(`controller_id`, `module_id`) USING BTREE ;

-- ----------------------------
-- Indexes structure for table `auth_user`
-- ----------------------------
CREATE INDEX `fk_auth_user_user_type1` ON `auth_user`(`user_type_id`) USING BTREE ;

-- ----------------------------
-- Indexes structure for table `controller`
-- ----------------------------
CREATE INDEX `fk_controller_module1` ON `controller`(`module_id`) USING BTREE ;

-- ----------------------------
-- Indexes structure for table `role_resource`
-- ----------------------------
CREATE INDEX `fk_role_resource_role1` ON `role_resource`(`role_id`) USING BTREE ;
CREATE INDEX `fk_role_resource_action1` ON `role_resource`(`action_id`, `controller_id`, `module_id`) USING BTREE ;

-- ----------------------------
-- Indexes structure for table `user_role`
-- ----------------------------
CREATE INDEX `fk_user_role_role1` ON `user_role`(`role_id`) USING BTREE ;
CREATE INDEX `fk_user_role_auth_user1` ON `user_role`(`user_id`) USING BTREE ;
